﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using Eassets.Layered.Domain.Asset;
using System.Collections.ObjectModel;

namespace Eassets.Data.Asset
{
    public class AssetData
    {
        /// <summary>
        /// Add new asset
        /// </summary>
        /// <param name="asset"></param>
        public void AddAsset(Assets asset)
        {
            SqlCommand command = new SqlCommand("SP_InsertAsset"); // Name of the SP
            command.Parameters.AddWithValue("@AssetCategoryId", asset.AssetCategoryId);
            command.Parameters.AddWithValue("@Code", asset.Code);
            command.Parameters.AddWithValue("@Description", asset.Description);
            command.Parameters.AddWithValue("@MoreDetails", asset.MoreDetails);
            command.Parameters.AddWithValue("@sys_DateCreation", asset.SysDateCreation);
            command.Parameters.AddWithValue("@sys_DateLastModification", asset.SysDateLastModification);
            CommonData commonData = new CommonData(); commonData.ExecuteNonQuery(ConstructCommand(command, asset));
        }

        /// <summary>
        /// Update an asset
        /// </summary>
        /// <param name="asset"></param>
        public void ModifyAsset(Assets asset)
        {
            SqlCommand command = new SqlCommand("SP_ModifyAsset"); // Name of the SP
            command.Parameters.AddWithValue("@AssetId", asset.AssetId);
            command.Parameters.AddWithValue("@AssetId", asset.AssetId);
            command.Parameters.AddWithValue("@AssetId", asset.AssetId);
            command.Parameters.AddWithValue("@AssetId", asset.AssetId);
            CommonData commonData = new CommonData();
            commonData.ExecuteNonQuery(command);
        }


        /// <summary>
        /// Delete a selected asset
        /// </summary>
        /// <param name="assetId"></param>
        public void DeleteAsset(long assetId)
        {
            SqlCommand command = new SqlCommand("sproc_t_c_DeleteAsset"); // Name of the SP
            command.Parameters.AddWithValue("@AssetId", assetId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); commonData.ExecuteNonQuery(command);
        }

        /// <summary>
        /// Get an asset by Id 
        /// </summary>
        /// <param name="assetId"></param>
        /// <returns></returns>
        public Assets GetByIdAsset(long assetId)
        {
            SqlCommand command = new SqlCommand("SP_GetAssetById");
            command.Parameters.AddWithValue("@AssetId", assetId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); SqlDataReader dataReader = commonData.ExecuteReader(command);
            Assets asset = new Assets();
            while (dataReader.Read())
            {
                if (!(String.IsNullOrEmpty(dataReader["AssetId"].ToString())))
                {
                    asset.AssetId = Convert.ToInt32(dataReader["AssetId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["AssetCategoryId"].ToString())))
                {
                    asset.AssetCategoryId = Convert.ToInt32(dataReader["AssetCategoryId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Code"].ToString())))
                {
                    asset.Code = Convert.ToString(dataReader["Code"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Description"].ToString())))
                {
                    asset.Description = Convert.ToString(dataReader["Description"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["MoreDetails"].ToString())))
                {
                    asset.MoreDetails = Convert.ToString(dataReader["MoreDetails"]);
                }
            }
            dataReader.Close();
            return asset;
        }

        /// <summary>
        /// Get all assets in to a list
        /// </summary>
        /// <returns></returns>
        public Collection<Assets> GetAllAsset()
        {
            Collection<Assets> assets = new Collection<Assets>();
            SqlCommand command = new SqlCommand("SP_GetAllAssets");
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); SqlDataReader dataReader = commonData.ExecuteReader(command);
            Assets asset;
            while (dataReader.Read())
            {
                asset = new Assets();
                if (!(String.IsNullOrEmpty(dataReader["AssetId"].ToString())))
                {
                    asset.AssetId = Convert.ToInt32(dataReader["AssetId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["AssetCategoryId"].ToString())))
                {
                    asset.AssetCategoryId = Convert.ToInt32(dataReader["AssetCategoryId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Code"].ToString())))
                {
                    asset.Code = Convert.ToString(dataReader["Code"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Description"].ToString())))
                {
                    asset.Description = Convert.ToString(dataReader["Description"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["MoreDetails"].ToString())))
                {
                    asset.MoreDetails = Convert.ToString(dataReader["MoreDetails"]);
                }

                assets.Add(asset);
            }
            dataReader.Close();
            return assets;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<Assets> GetAllAssetsNotInCompanyAssets()
        {
            List<Assets> assetResults;
            List<long> results;
            Collection<Assets> assets = GetAllAsset();
            List<long> assetsIds = (from id in assets
                                    select id.AssetId).ToList();
            CompanyAssetData companyAssetData = new CompanyAssetData();
            Collection<CompanyAsset> companyAssets = companyAssetData.GetAllCompanyAssets();
            List<long> companyAssetsIds = (from id in companyAssets
                                           select id.AssetId).ToList();

            results = assetsIds.Except(companyAssetsIds).ToList();
            assetResults = (from id in assets
                            where results.Contains(id.AssetId)
                            select id).ToList();

            return assetResults;

        }

        public SqlCommand ConstructCommand(SqlCommand command, Assets asset)
        {
            command.CommandType = CommandType.StoredProcedure;
            return command;
        }

    }
}
